package statistiche;

import java.sql.Connection;
import java.sql.SQLException;

import javax.swing.JPanel;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.jdbc.JDBCCategoryDataset;
import org.jfree.data.jdbc.JDBCPieDataset;

public class StatisticaCliente {

	private Connection conn;
	private int id;

	public StatisticaCliente(Connection conn, int id) {
		this.conn = conn;
		this.id = id;

	}

	public JPanel getStatisticaAnnuale() throws SQLException {
		String query = "select DATE_FORMAT(inizioPrestito,'%Y') as Anno, count(*) as Numero from StoricoCliente, Libri where StoricoCliente.Libri_isbn = Libri.isbn AND StoricoCliente.Cliente_idCliente = "
				+ id + " group by Anno";
		JDBCCategoryDataset dataset = new JDBCCategoryDataset(conn);
		dataset.executeQuery(query);
		JFreeChart chart = ChartFactory.createBarChart3D("Statistica Annuale",
				"Anno", "Numero Prestiti", dataset, PlotOrientation.VERTICAL,
				false, true, true);

		ChartPanel cp = new ChartPanel(chart);

		return cp;

	}

	public JPanel getStatisticaMensile() throws SQLException {
		String sql = "select DATE_FORMAT(inizioPrestito,'%M %Y') as Mese, count(*) as Numero from StoricoCliente, Libri where StoricoCliente.Libri_isbn = Libri.isbn AND StoricoCliente.Cliente_idCliente = "
				+ id + " group by Mese order by inizioPrestito ";
		JDBCCategoryDataset dataset = new JDBCCategoryDataset(conn);
		dataset.executeQuery(sql);
		JFreeChart chart = ChartFactory.createBarChart3D("Statistica Mensile",
				"Mese", "Numero Prestiti", dataset, PlotOrientation.VERTICAL,
				false, true, true);

		ChartPanel cp = new ChartPanel(chart);

		return cp;

	}

	public JPanel getStatisticaGeneri() throws SQLException {
		String sql = "select genere, count(*) as numero from StoricoCliente, Libri where StoricoCliente.Libri_isbn = Libri.isbn AND StoricoCliente.Cliente_idCliente = "
				+ id + " group by genere";
		JDBCPieDataset dataset = new JDBCPieDataset(conn);
		dataset.executeQuery(sql);
		JFreeChart chart = ChartFactory.createPieChart(
				"Statistica Generi Preferiti ", dataset, true, true, true);

		ChartPanel cp = new ChartPanel(chart);

		return cp;

	}

	public JPanel adminGetStatisticaGeneri() throws SQLException {
		String sql = "select genere, count(*) as numero from StoricoCliente, Libri where StoricoCliente.Libri_isbn = Libri.isbn  group by genere";
		JDBCPieDataset dataset = new JDBCPieDataset(conn);
		dataset.executeQuery(sql);
		JFreeChart chart = ChartFactory.createPieChart(
				"Statistica Generi Preferiti ", dataset, true, true, true);

		ChartPanel cp = new ChartPanel(chart);

		return cp;

	}

	public JPanel adminGetStatisticaMensile() throws SQLException {
		String sql = "select DATE_FORMAT(inizioPrestito,'%M %Y') as Mese, count(*) as Numero from StoricoCliente, Libri where StoricoCliente.Libri_isbn = Libri.isbn  group by Mese order by inizioPrestito ";
		JDBCCategoryDataset dataset = new JDBCCategoryDataset(conn);
		dataset.executeQuery(sql);
		JFreeChart chart = ChartFactory.createBarChart3D("Statistica Mensile",
				"Mese", "Numero Prestiti", dataset, PlotOrientation.VERTICAL,
				false, true, true);

		ChartPanel cp = new ChartPanel(chart);

		return cp;

	}

	public JPanel adminGetStatisticaAnnuale() throws SQLException {
		String query = "select DATE_FORMAT(inizioPrestito,'%Y') as Anno, count(*) as Numero from StoricoCliente, Libri where StoricoCliente.Libri_isbn = Libri.isbn group by Anno";
		JDBCCategoryDataset dataset = new JDBCCategoryDataset(conn);
		dataset.executeQuery(query);
		JFreeChart chart = ChartFactory.createBarChart3D("Statistica Annuale",
				"Anno", "Numero Prestiti", dataset, PlotOrientation.VERTICAL,
				false, true, true);

		ChartPanel cp = new ChartPanel(chart);

		return cp;

	}

}
